package com.googlecode.economini.issues;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

public class IssueStore extends SQLiteOpenHelper {

    /*
     * Database constants
     */
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "issue.db";

    /*
     * Table constants
     */
    public static final String ISSUE_TABLE = "issue";
    public static final String ARTICLE_TABLE = "article";
    public static final String ID_COLUMN = BaseColumns._ID;

    /*
     * Issue columns
     */
    public static final String TITLE_COLUMN = "title";
    public static final String URL_COLUMN = "url";
    public static final String STATE_COLUMN = "state";
    public static final String COVER_URL_COLUMN = "cover_url";
    public static final String COVER_IMG_COLUMN = "cover_img";

    /*
     * Article columns
     */
    public static final String ARTICLE_ISSUE_ID_COLUMN = "issue";
    public static final String ARTICLE_TITLE_COLUMN = "title";
    public static final String ARTICLE_URL_COLUMN = "url";
    public static final String ARTICLE_TEXT_COLUMN = "text";

    /** Table creation SQL */
    private static final String ISSUE_TABLE_CREATE = "CREATE TABLE " 
            + ISSUE_TABLE + " ("
            + ID_COLUMN + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + TITLE_COLUMN + " TEXT UNIQUE, "
            + URL_COLUMN + " TEXT NOT NULL, "
            + COVER_URL_COLUMN +" TEXT, "
            + STATE_COLUMN + " TEXT,"
            + COVER_IMG_COLUMN + " BLOB);";

    /** Table creation SQL */
    private static final String ARTICLE_TABLE_CREATE = "CREATE TABLE " 
            + ARTICLE_TABLE + " ("
            + ID_COLUMN + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + ARTICLE_ISSUE_ID_COLUMN + " INT NOT NULL," // TODO foreign key
            + ARTICLE_TITLE_COLUMN + " TEXT NOT NULL, "
            + ARTICLE_URL_COLUMN + " TEXT NOT NULL, "
            + ARTICLE_TEXT_COLUMN + " BLOB);";

    public IssueStore(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(ISSUE_TABLE_CREATE);
        db.execSQL(ARTICLE_TABLE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      db.execSQL("drop TABLE " + ISSUE_TABLE);
      db.execSQL("drop TABLE " + ARTICLE_TABLE);

      onCreate(db);
    }

}
